
package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

import DBM.DBManager;
import bean.MailBEAN;

import com.sun.org.apache.xerces.internal.impl.xpath.regex.ParseException;

public class MailDAO {
	private final String	addMail				= "INSERT INTO Mail( mailFromID, mailToID, title, content, dateTime, isRead) VALUES( ?, ?, ?, ?, ?, ? )";
	private final String	removeMail			= "DELETE FROM Mail WHERE mailID = ?";
	private final String	getNumberOfMails	= "SELECT COUNT(*) AS mails FROM Mail WHERE mailToID = ?";
	private final String	getMailsById		= "SELECT * FROM Mail WHERE mailToID = ?";
	private final String	getMailById			= "SELECT * FROM Mail WHERE mailID = ?";
	private final String	setMailRead			= "UPDATE Mail SET isRead = ? WHERE mailID = ?";
	private DBManager		dbmanager			= null;

	public MailDAO( DBManager DBM ) {
		this.dbmanager = DBM;
	}

	public void addMail( int mailFromID, int mailToID, String title,
			String content ) {
		try {
			PreparedStatement ppstm = dbmanager.makePreparedStatement( addMail );
			ppstm.setInt( 1, mailFromID );
			ppstm.setInt( 2, mailToID );
			ppstm.setString( 3, title );
			ppstm.setString( 4, content );
			ppstm.setString( 5, getCurrentTime() );
			ppstm.setBoolean( 6, false );
			ppstm.execute();
		} catch( SQLIntegrityConstraintViolationException e ) {
			e.printStackTrace();
		} catch( SQLException e ) {
			e.printStackTrace();
		}
	}

	public void removeMail( int mailID ) {
		try {
			PreparedStatement ppstm = dbmanager
					.makePreparedStatement( removeMail );
			ppstm.setInt( 1, mailID );
			ppstm.execute();
		} catch( SQLIntegrityConstraintViolationException e ) {
			e.printStackTrace();
		} catch( SQLException e ) {
			e.printStackTrace();
		}
	}

	public int getNumberOfMails( int mailToID ) {
		int mails = 0;
		try {
			PreparedStatement ppstm = dbmanager
					.makePreparedStatement( getNumberOfMails );
			ppstm.setInt( 1, mailToID );
			ResultSet res = ppstm.executeQuery();
			if( res.next() ) {
				mails = res.getInt( "mails" );
			}
		} catch( SQLException e ) {
			e.printStackTrace();
		}
		return mails;
	}

	public ArrayList< MailBEAN > getMailsByID( int mailToID ) {
		ArrayList< MailBEAN > list = new ArrayList< MailBEAN >();
		try {
			PreparedStatement ppstm = dbmanager
					.makePreparedStatement( getMailsById );
			ppstm.setInt( 1, mailToID );
			ResultSet res = ppstm.executeQuery();
			while( res.next() ) {
				MailBEAN mail = new MailBEAN( res.getInt( "mailID" ), res
						.getInt( "mailFromID" ), res.getInt( "mailToID" ), res
						.getString( "title" ), res.getString( "content" ),
						getCalendarFromString( res.getString( "dateTime" ) ),
						res.getBoolean( "isRead" ) );
				System.out.println( mail.toString() );
				list.add( mail );
			}
		} catch( SQLException e ) {
			e.printStackTrace();
		}
		return list;
	}

	public MailBEAN getMailByID( int mailID ) {
		MailBEAN mail = null;
		try {
			PreparedStatement ppstm = dbmanager
					.makePreparedStatement( getMailById );
			ppstm.setInt( 1, mailID );
			ResultSet res = ppstm.executeQuery();
			while( res.next() ) {
				mail = new MailBEAN( res.getInt( "mailID" ), res
						.getInt( "mailFromID" ), res.getInt( "mailToID" ), res
						.getString( "title" ), res.getString( "content" ),
						getCalendarFromString( res.getString( "dateTime" ) ),
						res.getBoolean( "isRead" ) );
			}
		} catch( SQLException e ) {
			e.printStackTrace();
		}
		return mail;
	}

	public void setMailRead( int mailID ) {
		try {
			PreparedStatement ppstm = dbmanager
					.makePreparedStatement( setMailRead );
			ppstm.setBoolean( 1, true );
			ppstm.setInt( 2, mailID );
			ppstm.execute();
		} catch( SQLIntegrityConstraintViolationException e ) {
			e.printStackTrace();
		} catch( SQLException e ) {
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * @param time
	 *            format = yyyy-MM-dd HH:mm:ss
	 * @return
	 */
	public Calendar getCalendarFromString( String time ) {
		if( time == null )
			return null;
		SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );
		Date date = null;
		try {
			date = (Date) sdf.parse( time );
		} catch( ParseException e ) {
			e.printStackTrace();
			return new GregorianCalendar();
		} catch( java.text.ParseException e ) {
			e.printStackTrace();
			return new GregorianCalendar();
		}
		Calendar cal = new GregorianCalendar();
		cal.setTime( date );
		return cal;
	}

	private String getCurrentTime() {
		Calendar cal = Calendar.getInstance();
		return getStringFromCalendar( cal.getTime() );
	}

	public String getStringFromCalendar( java.util.Date date ) {
		if( date == null )
			return "";
		SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );
		return sdf.format( date.getTime() );
	}
}
